//
//  DatabaseService.m
//  TheRicher
//
//  Created by 李晓东 on 2018/1/18.
//  Copyright © 2018年 PeanutXu. All rights reserved.
//

#import "DatabaseService.h"
#import "ShuangSeQiu.h"
#import <FMDB.h>

#define DatabaseName @"richer.db"
//static NSString  *dbName = @"DoubleChromosphere.db";
@interface DatabaseService()
@property (nonatomic, strong) FMDatabaseQueue *database;
@end

@implementation DatabaseService

+ (id)share{
    static DatabaseService *databaseService = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        databaseService = [[DatabaseService alloc]initDatabase];
    });
    return databaseService;
}

- (instancetype)initDatabase{
    if ( self = [super init] ){
        NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
        path = [path stringByAppendingPathComponent:DatabaseName];
        
        _database = [FMDatabaseQueue databaseQueueWithPath:path];
        [self initTable];
    }
    
    return self;
}

- (void)initTable{
    NSString *tablePath = [[NSBundle mainBundle]pathForResource:@"ShuangSeQiu" ofType:@"sql"];
    NSString *sqlInFile = [NSString stringWithContentsOfFile:tablePath encoding:NSUTF8StringEncoding error:NULL];
    [_database inDatabase:^(FMDatabase * _Nonnull db) {
        [db executeStatements:sqlInFile];
    }];

    
}

// MARK: 查询
- (void)queryTable:(NSString *)tableName withCondition:(NSString *)condition ofConditionName:(NSString *)conditionName andJudgeFlag:(NSString *)judgeFlag andOrderBy:(NSString *)columnName andSortBy:(NSInteger)sort andCountOf:(NSInteger)count {
//    SELECT * from T_ShuangSeQiu WHERE 1 = 1 AND issueno < 2018001 ORDER BY issueno desc LIMIT 20
    NSMutableString *mutableString = [NSMutableString string];
    [mutableString appendFormat:@"SELECT s.*, k.type1, k.type2, k.type3, k.type4, k.type5, k.type6, k.type7, k.type8, k.type9, k.type10, k.type11, k.type12, k.type13, k.type14, k.type15, k.type16, k.type17, k.type18, k.type19, k.type20, k.type21, k.type22, k.type23, k.type24, k.type25, k.type26, k.type27, k.type28, k.type29, k.type30, k.type31, k.type32  FROM %@ s LEFT OUTER  JOIN T_KillingNumber k ON s.issueno = k.issueno WHERE 1 = 1 ", tableName];
    if (conditionName != nil) {
        [mutableString appendString:[NSString stringWithFormat:@"and s.%@ %@ '%@ '", conditionName, judgeFlag, condition]];
    }
    [mutableString appendString:[NSString stringWithFormat:@"ORDER BY s.%@ %@ LIMIT %ld;", columnName, sort == 1 ? @"desc" : @"asc", count]];
    
    [_database inDatabase:^(FMDatabase * _Nonnull db) {
        [db open];
        NSMutableArray *resultArray = [NSMutableArray array];
        FMResultSet *rs = [db executeQuery:mutableString, nil];
        while ([rs next]) {
            NSMutableDictionary *tempDict = [NSMutableDictionary dictionary];
            for (int i = 0; i < [rs columnCount]; i++) {
                [tempDict setValue:[rs objectForColumnIndex:i] forKey:[rs columnNameForIndex:i]];
            }
            ShuangSeQiu *shuangSeQiu = [ShuangSeQiu shuangSeQiuWithDict:tempDict];
            [resultArray addObject:shuangSeQiu];
            
        }
        if ([self.delegate respondsToSelector:@selector(resultInDatabase:)]) {
            [self.delegate resultInDatabase:resultArray];
        }
        [db close];
    }];
    
}

- (void)insertTable:(NSString *)tableName withObject:(NSArray *)shuangSeQiuArray{
    [_database inDatabase:^(FMDatabase * _Nonnull db) {
        [db open];
        for (ShuangSeQiu *shuangSeQiu in shuangSeQiuArray) {
            BOOL flag = [db executeUpdate:@"insert into T_ShuangSeQiu (red1, red2, red3, red4, red5, red6, blue, issueno, opendate, caipiaoid ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", shuangSeQiu.red1, shuangSeQiu.red2, shuangSeQiu.red3, shuangSeQiu.red4, shuangSeQiu.red5, shuangSeQiu.red6, shuangSeQiu.blue, shuangSeQiu.issueno, shuangSeQiu.opendate, @11];
            NSLog(@"%d", flag);
        }
        [db close];
    }];
}

- (void)insertTableOfKillingNumberWithObjects:(NSArray *)shuangSeQiuArray inColumn:(NSString *)columnName{

    [_database inDatabase:^(FMDatabase * _Nonnull db) {
        [db open];
        for (ShuangSeQiu *shuangSeQiu in shuangSeQiuArray) {
            NSString *querySql = [NSString stringWithFormat:@"select * from T_KillingNumber where issueno == %@", shuangSeQiu.issueno];
            FMResultSet *resultSet = [db executeQuery:querySql, nil];
            NSString *sqlStatement = nil;
            if ([resultSet next]) {
                sqlStatement = [NSString stringWithFormat:@"UPDATE T_KillingNumber SET %@ = ? WHERE issueno = ?", columnName];

            } else {
                sqlStatement = [NSString stringWithFormat:@"insert into T_KillingNumber ( %@, 'issueno') values (?, ?)", columnName];

            }
            BOOL flag = [db executeUpdate:sqlStatement, shuangSeQiu.killingNumber, shuangSeQiu.issueno];
//
            NSLog(@"%d", flag);
        }
        [db close];
    }];
}
@end
